import pymysql
import xlrd

connect = pymysql.connect(host='localhost', user='root', password='123456')
cursor = connect.cursor()
sql = 'create database baidu character set utf8'
cursor.execute(sql)
connect = pymysql.connect(host='localhost', user='root', password='123456',database='baidu')
cursor = connect.cursor()

sql = 'create table `user`(序号 int primary key, 工牌号 int(12),花名 char(100), 真实姓名 char(100) ,密码 int , 电话号码 int,邮箱 char(100),年龄 int,性别 char,居住地址 char(100),入职日期 char(100) , 薪资 float , 职责 char(100) , 外包公司 char(100))'
cursor.execute(sql)

wb = xlrd.open_workbook(filename='E:\培训资料\操作数据库表格\员工的人员信息.xls')
sheet = wb.sheet_by_index(0)
for i in range(1, sheet.nrows):
    x = sheet.row_values(i)
    x[0] = int(x[0])
    x[1] = int(x[1])
    x[4] = int(x[4])
    x[5] = int(x[5])
    x[7] = int(x[7])

    sql = f'insert into user value({x[0]},{x[1]},"{x[2]}","{x[3]}",{x[4]},{x[5]},"{x[6]}",{x[7]},"{x[8]}","{x[9]}","{x[10]}",{x[11]},"{x[12]}","{x[13]}")'
    cursor.execute(sql)

connect.commit()
